package com.zzn.bookManage.util;

import java.io.File;
import java.sql.*;

/**
 * 数据库连接 初始化
 */
public class DBUtil {

    private static Connection connection = null;

    /**
     * 使用单例模式获取 connection
     * @return
     */
    public static Connection getConnection() {
        MyProperties config = ObjectHolder.getInstance().getConfig();
        File dbFile = new File(config.getDbFile());
        // 连接创建好会自动建立文件
        boolean newFile = !dbFile.exists();
        boolean cleanDb = config.getDbRestore().equals("true");
        String url = "jdbc:sqlite:" + dbFile.getAbsolutePath();
        if (connection == null){
            try {
                // 获取连接
                Class.forName("org.sqlite.JDBC");
                connection = DriverManager.getConnection(url);
                // 初始化数据库
                if (newFile){
                    initDataBase();
                }else if (cleanDb){
                    dropDataBase();
                    initDataBase();
                }
                System.out.println("==== 连接数据库成功 ====");
                System.out.println("==== url: "+url+" ====");
            } catch (SQLException throwables) {
                System.out.println("!!!! 连接数据库失败 !!!!");
                throwables.printStackTrace();
            } catch (ClassNotFoundException e) {
                System.out.println("!!!! 缺少数据库驱动 !!!!");
                e.printStackTrace();
            }
        }
        return connection;
    }

    /**
     * 初始化数据库连接
     */
    private static void initDataBase() {

        // 建表语句
        String tableStructure1 = "CREATE TABLE IF NOT EXISTS `books`(" +
                "`bid` INTEGER primary key autoincrement," +
                "`name` TEXT not null," +
                "`author` TEXT not null, " +
                "`publisher` TEXT not null, " +
                "`description` TEXT , " +
                "`price` REAL not null, " +
                "`position` TEXT not null, " +
                "`borrow_by` TEXT , " +
                "`expire_time` TIMESTAMP , " +
                "`create_time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP " +
                ")";
        String tableStructure2 = "CREATE TABLE IF NOT EXISTS `users`(" +
                "`uid` INTEGER primary key AUTOINCREMENT," +
                "`username` TEXT not null," +
                "`nickname` TEXT not null, " +
                "`password` TEXT not null " +
                ")";
        String tableStructure3 = "insert into `users` (`username`,`nickname`,`password`) values ('admin','ADMIN','1234qwer')";

        try {
            Statement statement = connection.createStatement();
            statement.execute(tableStructure1);
            statement.execute(tableStructure2);
            statement.executeUpdate(tableStructure3);
            System.out.println("==== 初始化数据库成功 ====");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    /**
     *  清除数据库内容
     */
    private static void dropDataBase() {
        // 删表语句
        String tableStructure1 = "DROP TABLE IF EXISTS `books`";
        String tableStructure2 = "DROP TABLE IF EXISTS `users`";

        try {
            Statement statement = connection.createStatement();
            statement.executeUpdate(tableStructure1);
            statement.executeUpdate(tableStructure2);

            System.out.println("==== 清除数据库成功 ====");
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

}
